package com.wisemoresystem.seal.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.ne.framework.data.page.Page;
import org.ne.framework.data.util.ChkTools;
import org.springframework.stereotype.Service;

import com.wisemoresystem.seal.base.service.BaseService;
import com.wisemoresystem.seal.util.DateTools;
import com.wisemoresystem.seal.util.SpringTools;
import com.wisemoresystem.seal.web.WebTools;

/**
 * 各种明细表
 * 
 * @author hxj
 */
@Service
public class AllKindsOfDetailService extends BaseService {

	// 印章申请明细表
	public List<Map<String, Object>> selectSealInstallList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,tp.name type_name,tc.name config_name,tb.name apply_bank_name,tch.bank_id c_bank_id, "
				+ " tm.verify_bank_id m_bank_id,tba.name use_bank_name,tch.verify v_flag,tm.flag f_flag,"
				+ " tch.verify_time c_time,tm.verify_time m_time from t_seal_new t "
				+ " left join t_check tch on t.check_id=tch._id and tch.verify_time is not null "
				+ " left join t_seal_workflow tw on t.workflow_id=tw._id "
				+ " left join t_seal_workflow_module tm on tw._id=tm.workflow_id and tm.verify_time is not null"
				// +
				// " ((select t.*,tch.verify verify_type,tb._id operate_id,'复核' as operate from t_seal_new t "
				// + " left join t_check tch on t.check_id=tch._id "
				// + " left join t_bank tb on tch.bank_id=tb._id "
				// + " where t.status=0 and t.check_id is not null) union "
				// +
				// " (select t.*,tm.flag verify_type,tb._id operate_id,'审核' as operate from t_seal_new t "
				// + " left join t_seal_workflow tw on t.workflow_id=tw._id "
				// +
				// " left join t_seal_workflow_module tm on tw._id=tm.workflow_id "
				// + " left join t_bank tb on tm.verify_bank_id=tb._id "
				// + " where t.status=0 and t.workflow_id is not null)) t  "
				+ " left join t_seal_type tp on t.seal_type_id=tp._id "
				+ " left join t_seal_type_config tc on t.seal_type_config_id=tc._id"
				+ " left join t_bank tb on t.user_bank_id=tb._id "
				+ " left join t_bank tba on t.use_bank_id=tba._id "
				+ " where t.status=0 and tb.name like ? and tba.name like ? "
				+ " and ((t.created>=? and t.created<=?) or (tch.verify_time>=? and tch.verify_time<=?) "
				+ " or (tm.verify_time>=? and tm.verify_time<=?)) "
				+ " and (tch.bank_id=? or tm.verify_bank_id=? or t.user_bank_id=?) "// ***********
				+ " order by t.created desc,t._id asc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and tb.name like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql.replace("and tba.name like ?", "");
		} else {
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql
					.replace(
							"and ((t.created>=? and t.created<=?) or (tch.verify_time>=? and tch.verify_time<=?) "
									+ " or (tm.verify_time>=? and tm.verify_time<=?))",
							"");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(WebTools.getCurrentBankId());
		params.add(WebTools.getCurrentBankId());
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	// 印章刻制明细
	public List<Map<String, Object>> selectSealEngravingList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,ts.title,tb.uname,tp.name type_name,ts.factory,ts.factory_linkman,ts.factory_mobile from t_seal_new_factory_log t"
				+ " left join t_seal_new ts on t.seal_new_id=ts._id "
				+ " left join t_seal_type tp on ts.seal_type_id=tp._id "
				+ " left join t_user tb on t.user_id=tb._id "
				+ " where t.status=0 and tb.uname like ? and t.start>=? and t.start<=? and t.user_bank_id=? "
				+ " order by t.created desc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and tb.uname like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.start>=? and t.start<=?", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	// 印章停用明细
	public List<Map<String, Object>> selectSealStopList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,tp.name,ts.true_code,tu.uname,tb.name bank_name from t_seal_stop t "
				+ " left join t_seal ts on ts._id= t.seal_id "
				+ " left join t_seal_new tn on ts.seal_new_id=tn._id "
				+ " left join t_seal_type tp on tn.seal_type_id=tp._id "
				+ " left join t_bank tb on t.bank_id=tb._id "
				+ " left join t_user tu on t.user_id=tu._id "
				+ " where t.status=0 and ts.true_code like ? and tu.uname like ? and t.created>=? and t.created<=? and t.bank_id=? "
				+ " order by t.created desc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and ts.true_code like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.created>=? and t.created<=?", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	// 印章销毁明细
	public List<Map<String, Object>> selectSealDestroyList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,tp.name,ts.true_code,tu.uname,tus.uname v_uname,tue.uname e_uname,tur.uname r_uname,tb.name bank_name "
				+ " ,tv.check_time,tv.check_flag,tv.review_flag,tv.review_time from t_seal_destory t "
				+ " left join t_seal_destroy_verify tv on t.seal_id=tv.seal_id "
				// +
				// " (select t.seal_id,t.user_id,t.apply_time operate_time,t.bank_id,'申请' as operate from t_seal_destory t where t.status=0 "
				// +
				// " union select t.seal_id,t.verify_user_id user_id,t.verify_time operate_time,t.bank_id,'销毁' as operate from t_seal_destory t "
				// + " where t.status=0 and t.verify_time is not null "
				// +
				// " union select t.seal_id,t.check_user_id user_id,t.check_time operate_time,t.check_bank_id bank_id,'审核' as operate from t_seal_destroy_verify t "
				// + " where t.status=0 and t.check_time is not null "
				// +
				// " union select t.seal_id,t.review_user_id user_id,t.review_time operate_time,t.review_bank_id bank_id,'复核' as operate from t_seal_destroy_verify t "
				// + " where t.status=0 and t.review_time is not null ) t "
				+ " left join t_seal ts on ts._id= t.seal_id "
				+ " left join t_seal_new tn on ts.seal_new_id=tn._id "
				+ " left join t_seal_type tp on tn.seal_type_id=tp._id "
				+ " left join t_bank tb on t.bank_id=tb._id "
				+ " left join t_user tu on t.user_id=tu._id "
				+ " left join t_user tus on t.verify_user_id=tus._id "
				+ " left join t_user tue on tv.check_user_id=tue._id "
				+ " left join t_user tur on tv.review_user_id=tur._id "
				+ " where t.status=0 and ts.true_code like ? and ((tu.uname like ?) or (tus.uname like ?) or (tue.uname like ?) or (tur.uname like ?)) "
				+ " and t.apply_time>=? and t.apply_time<=? and t.bank_id=? "
				+ " order by t.created desc,t.seal_id asc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and ts.true_code like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace(
							"and ((tu.uname like ?) or (tus.uname like ?) or (tue.uname like ?) or (tur.uname like ?))",
							"");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.apply_time>=? and t.apply_time<=?", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	// 印章下发明细
	public List<Map<String, Object>> selectSealPushlList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,ts.true_code,tu.uname,a.uname c_uname,b.uname ga_uname,"
				+ " c.uname o_uname,d.uname i_uname,e.uname re_uname,tp.name type_name,tbb.name push_bank,"
				+ " tb.name recieve_bank,tc.verify_time c_time,tch.verify_time ch_time,tc.verify_bank_id c_bank_id, "
				+ " tch.verify_bank_id ch_bank_id,tc.verify c_verify,tch.verify ch_verify from t_seal_push t "
				+ " left join t_check tc on t.check_id=tc._id "
				+ " left join t_check tch on t.re_check_id=tch._id "
				// +
				// " (select t.*,t.bank_id operate_id,t.user_id operate_user_id,t.created time, "
				// + " '下发' operate from t_seal_push t where t.status=0 "
				// +
				// " union select t.*,tc.verify_bank_id operate_id,tc.verify_user_id operate_user_id, "
				// + " tc.verify_time time,'下发复核' operate from t_seal_push t "
				// +
				// " left join t_check tc on t.check_id=tc._id where t.status=0 and t.check_id is not null"
				// +
				// " union select t.*,t.down_bank_id operate_id,t.rec_app_user_id operate_user_id, "
				// +
				// " t.code_time time,'领用申请' operate from t_seal_push t where t.status=0 and t.code_time is not null "
				// +
				// " union select t.*,t.bank_id operate_id,t.user_id operate_user_id,t.verify_time time, '出库' operate "
				// +
				// "  from t_seal_push t where t.status=0 and t.verify_time is not null "
				// +
				// " union select t.*,t.down_bank_id operate_id,t.recieve_user_id operate_user_id,t.recieve_time time, "
				// +
				// " '入库' operate from t_seal_push t where t.status=0 and t.recieve_time is not null "
				// +
				// " union select t.*,tc.verify_bank_id operate_id,tc.verify_user_id operate_user_id "
				// + " ,tc.verify_time time,'领取复核' operate from t_seal_push t "
				// +
				// " left join t_check tc on t.re_check_id=tc._id where t.status=0 and t.re_check_id is not null) t"
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " left join t_seal_new tn on ts.seal_new_id=tn._id "
				+ " left join t_seal_type tp on tn.seal_type_id=tp._id "
				+ " left join t_user tu on t.user_id=tu._id " // apply
				+ " left join t_user a on tc.verify_user_id=a._id "// check
				+ " left join t_user b on t.rec_app_user_id=b._id "// get apply
				+ " left join t_user c on t.user_id=c._id "// out
				+ " left join t_user d on t.recieve_user_id=d._id "// in
				+ " left join t_user e on tch.verify_user_id=e._id "// in check
				+ " left join t_bank tb on t.down_bank_id=tb._id "
				+ " left join t_bank tbb on t.bank_id=tbb._id "
				+ " where t.status=0 and ts.true_code like ? and (tu.uname like ? or a.uname like ? or b.uname like ? "
				+ " or c.uname like ? or d.uname like ? or e.uname like ?)  "
				+ " and ((t.created>=? and t.created<=?) or (tc.verify_time>=? and tc.verify_time<=?) or "
				+ " (t.code_time>=? and t.code_time<=?) or (t.verify_time>=? and t.verify_time<=?) "
				+ " or (t.recieve_time>=? and t.recieve_time<=?) or (tch.verify_time>=? and tch.verify_time<=?)) "
				+ " and (t.bank_id=? or t.down_bank_id=?) order by t.created desc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and ts.true_code like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace(
							"and (tu.uname like ? or a.uname like ? or b.uname like ? "
									+ " or c.uname like ? or d.uname like ? or e.uname like ?)",
							"");
		} else {
			for (int i = 0; i < 6; i++) {
				params.add("%" + srh_name + "%");
			}
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql
					.replace(
							"and ((t.created>=? and t.created<=?) or (tc.verify_time>=? and tc.verify_time<=?) or "
									+ " (t.code_time>=? and t.code_time<=?) or (t.verify_time>=? and t.verify_time<=?) "
									+ " or (t.recieve_time>=? and t.recieve_time<=?) or (tch.verify_time>=? and tch.verify_time<=?))",
							"");
		} else {
			for (int i = 0; i < 6; i++) {
				params.add(srh_start);
				params.add(DateTools.addOneDay(srh_end));
			}
		}
		params.add(WebTools.getCurrentBankId());
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	// 印章移植明细
	public List<Map<String, Object>> selectSealTransferList(String srh_uname,
			String srh_name, String srh_start, String srh_end, Page page) {
		String sql = "select t.*,tu.uname,tus.uname done_name from t_seal_auth t"
				+ " left join t_user tu on t.begin_user_id=tu._id "
				+ " left join t_user tus on t.end_user_id=tus._id "
				+ " where t.status=0 and tu.uname like ? and tus.uname like ? and t.created>=? and t.created<=? and t.bank_id=? "
				+ " order by t.created desc ";

		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_uname)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_uname + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql.replace("and tus.uname like ?", "");
		} else {
			params.add("%" + srh_name + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.created>=? and t.created<=? ", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(WebTools.getCurrentBankId());
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);
		return list;
	}

	public void delAll(String up_code) {
		String sql = "select * from t_menu t where t.up_code = ?";
		List<Map<String, Object>> list = this.jt.queryForList(sql, up_code);
		for (Map<String, Object> map : list) {
			String code = (String) map.get("code");
			delAll(code);
		}

		String usql = "update t_menu set status=-100 where code=?";
		this.jt.update(usql, up_code);
	}

	public static void main(String[] args) {
		AllKindsOfDetailService s = SpringTools
				.getBean(AllKindsOfDetailService.class);
		s.delAll("sealmanage");
	}
}
